package com.feng.ALittleFlower;
import java.sql.*;
import java.util.ArrayList;

public class Database {
    private static final String DRIVERS = "org.mariadb.jdbc.Driver";
    private static final String URL = "jdbc:mariadb://localhost:3306/final";
    private static final String USERNAME = "beautiful";
    private static final String PASSWORD = null;

    private static Connection getConnection() throws SQLException, ClassNotFoundException {
        System.setProperty("jdbc.drivers", DRIVERS);
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }
    
    public static void createAccount(String name, String password) throws SQLException, ClassNotFoundException {
        try (Connection conn = getConnection();
             Statement stat = conn.createStatement();) {
            String cmd = "INSERT INTO Account (name, password) VALUES ('" + name + "', '" + password +"')";
            stat.executeUpdate(cmd);
        }
    }
    
    private static int getUserId(String name) throws SQLException, ClassNotFoundException {
        try (Connection conn = getConnection();
             Statement stat = conn.createStatement();
             ResultSet result = stat.executeQuery("SELECT * FROM Account WHERE name='" + name + "'");) {
            if (result.next()) return result.getInt("id");
        }
        return -1;
    }


    public static ArrayList<Comment> getComments() throws SQLException, ClassNotFoundException {
        ArrayList<Comment> comments = new ArrayList<>();
        try (Connection conn = getConnection();
             Statement stat = conn.createStatement();
             ResultSet result = stat.executeQuery("SELECT * FROM Comment ORDER BY message_id DESC");) {
            while (result.next()) {
                int id = result.getInt("user_id");
                int rate = result.getInt("rate");
                String content = result.getString("content");
                String username = getSQLUsername(id);
                Comment comment = new Comment(username, rate, content);
                comments.add(comment);
            }
        }
        return comments;
    }

    private static String getSQLUsername(int id) throws SQLException, ClassNotFoundException {
        try (Connection conn = getConnection();
             Statement stat = conn.createStatement();
             ResultSet result = stat.executeQuery("SELECT * FROM Account WHERE id='" + id +"'");){
            if (result.next()) {
                return result.getString("name");
            }
            else {
                return null;
            }
        }
    }
    
    private static String getUserPassword(String name) throws SQLException, ClassNotFoundException {
        try (Connection conn = getConnection();
             Statement stat = conn.createStatement();
             ResultSet result = stat.executeQuery("SELECT * FROM Account WHERE name='" + name + "'");) {
            if (result.next()) return result.getString("password");
        }
        return null;
    }

    public static void addComment(String name, int rate, String content) throws SQLException, ClassNotFoundException {
        try (Connection conn = getConnection();
             Statement stat = conn.createStatement();) {
            int userId = getUserId(name);
            String cmd = "INSERT INTO Comment (user_id, rate, content) VALUES (" + userId + ", " + rate +", '" +
                    content + "')";
            System.out.println(cmd);
            stat.executeUpdate(cmd);
        }
    }
    
    public static boolean isPasswordMatched(String name, String password) throws SQLException, ClassNotFoundException {
        return getUserPassword(name).equals(password);
    }
    
    public static boolean isSQLUserExists(String name) throws SQLException, ClassNotFoundException {
        return getUserId(name) != -1;
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        addComment("a", 9, "很不错的电影哦");
    }
}
